package controllers;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lib.UtilsInput;
import models.Reporte5;

import com.google.gson.Gson;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

import play.db.DB;
import play.mvc.Controller;

public class Reporte_5 extends Controller {
	
	public static void Reporte_5() {
		if(Security.isAuthenticated())
			render();
		else
			Application.login();
	}
	
	public static void getReporte5(String from,String to,String selec){
		String query = "select count(*) as cantidad,cot_servicio,emp_nombres from sigpc_cotizacion sigcot join sigpc_empleado sigemp on sigcot.cot_id_emp=sigemp.emp_id where cot_estado = 3 group by cot_servicio,emp_nombres";
		String str = null;
		Gson gson = new Gson();
    	Connection conn = DB.getConnection();
    	
    	//Lista de objetos 
    	List<Reporte5> listReporte5 = new ArrayList<Reporte5>();
    	System.out.println(from);
    	
    	try {
    		if (from != null &&  to != null && selec != null)
    		{
    			if (!from.equalsIgnoreCase("") &&  !to.equalsIgnoreCase("") && selec.equalsIgnoreCase("0"))
        			query = "select count(*) as cantidad,cot_servicio,emp_nombres from sigpc_cotizacion sigcot join sigpc_empleado sigemp on sigcot.cot_id_emp=sigemp.emp_id WHERE cot_fecha BETWEEN '" + from + "' AND '" + to + "' AND cot_estado = 3 group by cot_servicio,emp_nombres ";
    			
    			if (!from.equalsIgnoreCase("") &&  !to.equalsIgnoreCase("") && !selec.equalsIgnoreCase("0"))
					query = "select count(*) as cantidad,cot_servicio,emp_nombres from sigpc_cotizacion sigcot join sigpc_empleado sigemp on sigcot.cot_id_emp=sigemp.emp_id WHERE cot_fecha BETWEEN '" + from + "' AND '" + to + "' AND cot_servicio = '"+ selec +"' AND cot_estado = 3 group by cot_servicio,emp_nombres ";
    		}	
    			
    		
        	ResultSet rs = conn.createStatement().executeQuery(query);
        	
        	 while (rs.next()) {
        		 Reporte5 reporte5 = new Reporte5(rs.getString("emp_nombres"),rs.getString("cot_servicio"),rs.getInt("cantidad"));
        		 listReporte5.add(reporte5);
        		 
        	        	
        	 }
    		
        	 str = "{\"success\":true ,\"records\":" + listReporte5.size()
     				+ ", \"total\":1 , \"page\":1, \"rows\":" + gson.toJson(listReporte5)
     				+ "}";

     		renderJSON(str);
     		
        	        	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//JASPER REPORT
	public static void  download_report(String from, String to, String servicio, String formato) {
	    Map reportParams = new HashMap();
	    
	   if (formato != null)
	   {
	  reportParams.put("from", from);
	  reportParams.put("to",  to);
	  reportParams.put("servicio",  servicio);
	  
	    
	  if (formato.equalsIgnoreCase("html"))
		renderHtml( UtilsInput.toString((ByteArrayInputStream) lib.BaseJasperReport.generateReport("reporte_5", reportParams,formato)));
		
	  else
		  renderBinary(lib.BaseJasperReport.generateReport("reporte_5", reportParams,formato), "reporte_5."+ formato);
	   
	   }
	  }
	
}
